﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace AliCMS.CourseModule.OleDb
{
    public class Member : OleDbBase, Dal.IMember
    {
        public int GetInnerIDFromGuid(Guid guid)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Member.ID, MapType(Alias.Member.ID)).Value = guid;
            Command.CommandText = "SELECT " + MapColumn(Alias.Member.InnerID) + " FROM " + MapTable(Alias.Objects.Member) + " WHERE " + MapColumn(Alias.Member.ID) + "=?";
            object result = Command.ExecuteScalar();
            if (result == null)
                return 0;
            else
                return (int)result;
        }
        public Guid GetGuidFromInnerID(object id)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Member.InnerID, MapType(Alias.Member.InnerID)).Value = id;
            Command.CommandText = "SELECT " + MapColumn(Alias.Member.ID) + " FROM " + MapTable(Alias.Objects.Member) + " WHERE " + MapColumn(Alias.Member.InnerID) + "=?";
            object result = Command.ExecuteScalar();
            if (result == null)
                return Guid.Empty;
            else
                return (Guid)result;
        }
        public Dictionary<string, object> Get(object id)
        {
            Dictionary<string, object> result = new Dictionary<string, object>();

            StringBuilder sql = new StringBuilder("SELECT TOP 1");
            sql.Append(" " + MapColumn(Alias.Member.ID));
            sql.Append("," + MapColumn(Alias.Member.InnerID));
			sql.Append("," + MapColumn(Alias.Member.No));
			sql.Append("," + MapColumn(Alias.Member.LevelID));
			sql.Append("," + MapColumn(Alias.Member.UserID));
			sql.Append("," + MapColumn(Alias.Member.UserName));
			sql.Append("," + MapColumn(Alias.Member.UserNickname));
			sql.Append("," + MapColumn(Alias.Member.UserRealName));
			sql.Append("," + MapColumn(Alias.Member.UserMobile));
			sql.Append("," + MapColumn(Alias.Member.UserHead));
            sql.Append("," + MapColumn(Alias.Member.AgentID));
            sql.Append("," + MapColumn(Alias.Member.AgentName));
			sql.Append("," + MapColumn(Alias.Member.AgentNickname));
			sql.Append("," + MapColumn(Alias.Member.AgentHead));
			sql.Append("," + MapColumn(Alias.Member.ExpiredTime));
            sql.Append("," + MapColumn(Alias.Member.CreatorID));
			sql.Append("," + MapColumn(Alias.Member.CreatorName));
			sql.Append("," + MapColumn(Alias.Member.CreateTime));
			sql.Append("," + MapColumn(Alias.Member.IsDeleted));
            sql.Append(" FROM " + MapTable(Alias.Objects.Member));

            if (id is Guid)
            {
                sql.Append(" WHERE " + MapColumn(Alias.Member.ID) + "=?");

                Command.Parameters.Clear();
                Command.Parameters.Add("@" + Alias.Member.ID, MapType(Alias.Member.ID)).Value = id;
            }
            else
            {
                sql.Append(" WHERE " + MapColumn(Alias.Member.InnerID) + "=?");

                Command.Parameters.Clear();
                Command.Parameters.Add("@" + Alias.Member.InnerID, MapType(Alias.Member.InnerID)).Value = id;
            }

            Command.CommandText = sql.ToString();
            DataReader = Command.ExecuteReader();
            if (!DataReader.HasRows)
            {
                DataReader.Close();
                return null;
            }

            DataReader.Read();
            int i = 0;
            result[Alias.Member.ID] = DataReader[i++];
            result[Alias.Member.InnerID] = DataReader[i++];
			result[Alias.Member.No] = DataReader[i++];
			result[Alias.Member.LevelID] = DataReader[i++];
			result[Alias.Member.UserID] = DataReader[i++];
			result[Alias.Member.UserName] = DataReader[i++];
			result[Alias.Member.UserNickname] = DataReader[i++];
			result[Alias.Member.UserRealName] = DataReader[i++];
			result[Alias.Member.UserMobile] = DataReader[i++];
			result[Alias.Member.UserHead] = DataReader[i++];
			result[Alias.Member.AgentID] = DataReader[i++];
			result[Alias.Member.AgentName] = DataReader[i++];
			result[Alias.Member.AgentNickname] = DataReader[i++];
			result[Alias.Member.AgentHead] = DataReader[i++];
			result[Alias.Member.ExpiredTime] = DataReader[i++];
            result[Alias.Member.CreatorID] = DataReader[i++];
			result[Alias.Member.CreatorName] = DataReader[i++];
			result[Alias.Member.CreateTime] = DataReader[i++];
			result[Alias.Member.IsDeleted] = DataReader[i++];
            DataReader.Close();

            return result;
        }
        public void Add(Dictionary<string, object> item)
        {
            #region No
            //获取初始编号
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.MemberLevel.ID, MapType(Alias.MemberLevel.ID)).Value = item[Alias.Member.LevelID];
            Command.CommandText = "SELECT TOP 1 " + MapColumn(Alias.MemberLevel.InitialNo) + " FROM " + MapTable(Alias.Objects.MemberLevel) + " WHERE " + MapColumn(Alias.MemberLevel.ID) + "=?";
            int initialNo = Convert.ToInt32(Command.ExecuteScalar());

            //获取会员数量
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Member.LevelID, MapType(Alias.Member.LevelID)).Value = item[Alias.Member.LevelID];
            Command.CommandText = "SELECT TOP 1 " + MapColumn(Alias.Member.No) + " FROM " + MapTable(Alias.Objects.Member) + " WHERE " + MapColumn(Alias.Member.LevelID) + "=? ORDER BY " + MapColumn(Alias.Member.No) + " DESC";
            object scalar = Command.ExecuteScalar();
            int no = initialNo;
            if (scalar != null && !Convert.IsDBNull(scalar))
                no = Convert.ToInt32(scalar);
            no++;

            do
            {
                Command.Parameters.Clear();
                Command.Parameters.Add("@" + Alias.Member.No, MapType(Alias.Member.No)).Value = no;
                Command.CommandText = "SELECT COUNT(*) FROM " + MapTable(Alias.Objects.Member) + " WHERE " + MapColumn(Alias.Member.No) + "=?";
                if (Convert.ToInt32(Command.ExecuteScalar()) == 0)
                    break;

                no++;
            }
            while (true);

            item[Alias.Member.No] = no;
            #endregion

            #region 设置RowLang
            item.Add(Alias.Member.RowLang, "|" + Lang + "|");
            #endregion

            StringBuilder names = new StringBuilder();
            StringBuilder values = new StringBuilder();

            Command.Parameters.Clear();
            PrepareParameters(names, values, item);

            Command.CommandText = "INSERT INTO " + MapTable(Alias.Objects.Member) + "(" + names + ") VALUES(" + values + ")";
            Command.ExecuteNonQuery();
        }
        public void Modify(int id, Dictionary<string, object> changes)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Member.InnerID, MapType(Alias.Member.InnerID)).Value = id;

            #region 检查并设置RowLang
            Command.CommandText = "SELECT " + MapColumn(Alias.Member.RowLang) + " FROM " + MapTable(Alias.Objects.Member) + " WHERE " + MapColumn(Alias.Member.InnerID) + "=?";
            string rowLang = Command.ExecuteScalar().ToString();
            if (!rowLang.Contains("|" + Lang + "|"))
                changes.Add(Alias.Member.RowLang, rowLang + Lang + "|");
            #endregion

            StringBuilder sql = new StringBuilder("UPDATE " + MapTable(Alias.Objects.Member) + " SET ");
            Command.Parameters.Clear();
            PrepareParameters(sql, changes);
            Command.Parameters.Add("@" + Alias.Member.InnerID, MapType(Alias.Member.InnerID)).Value = id;

            sql.Append(" WHERE " + MapColumn(Alias.Member.InnerID) + "=?");

            Command.CommandText = sql.ToString();
            Command.ExecuteNonQuery();
        }
        public void Delete(int id)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Member.InnerID, MapType(Alias.Member.InnerID)).Value = id;

            StringBuilder sql = new StringBuilder("DELETE FROM " + MapTable(Alias.Objects.Member) + " WHERE " + MapColumn(Alias.Member.InnerID) + "=?");

            Command.CommandText = sql.ToString();
            Command.ExecuteNonQuery();
        }
        public DataTable List(int page, int pageSize, string condition, string sort)
        {
            DataTable result = new DataTable();
            result.Columns.Add(Alias.Member.ID, MapCSType(Alias.Member.ID));
            result.Columns.Add(Alias.Member.InnerID, MapCSType(Alias.Member.InnerID));
			result.Columns.Add(Alias.Member.No, MapCSType(Alias.Member.No));
			result.Columns.Add(Alias.Member.LevelID, MapCSType(Alias.Member.LevelID));
			result.Columns.Add(Alias.Member.UserID, MapCSType(Alias.Member.UserID));
			result.Columns.Add(Alias.Member.UserName, MapCSType(Alias.Member.UserName));
			result.Columns.Add(Alias.Member.UserNickname, MapCSType(Alias.Member.UserNickname));
			result.Columns.Add(Alias.Member.UserRealName, MapCSType(Alias.Member.UserRealName));
			result.Columns.Add(Alias.Member.UserMobile, MapCSType(Alias.Member.UserMobile));
			result.Columns.Add(Alias.Member.UserHead, MapCSType(Alias.Member.UserHead));
			result.Columns.Add(Alias.Member.AgentID, MapCSType(Alias.Member.AgentID));
			result.Columns.Add(Alias.Member.AgentName, MapCSType(Alias.Member.AgentName));
			result.Columns.Add(Alias.Member.AgentNickname, MapCSType(Alias.Member.AgentNickname));
			result.Columns.Add(Alias.Member.AgentHead, MapCSType(Alias.Member.AgentHead));
			result.Columns.Add(Alias.Member.ExpiredTime, MapCSType(Alias.Member.ExpiredTime));
            result.Columns.Add(Alias.Member.CreatorID, MapCSType(Alias.Member.CreatorID));
			result.Columns.Add(Alias.Member.CreatorName, MapCSType(Alias.Member.CreatorName));
			result.Columns.Add(Alias.Member.CreateTime, MapCSType(Alias.Member.CreateTime));
			result.Columns.Add(Alias.Member.IsDeleted, MapCSType(Alias.Member.IsDeleted));

            #region 条件
            Command.Parameters.Clear();
            StringBuilder where = new StringBuilder();
            if (!String.IsNullOrEmpty(condition))
            {
                where.Append(" WHERE " + Alias.Member.RowLang + " LIKE '%|" + Lang + "|%' AND ");
                where.Append("(" + condition + ")");

                ParseCondition(where, "");
            }
            else
            {
                where.Append(" WHERE " + MapColumn(Alias.Member.RowLang) + " LIKE '%|" + Lang + "|%'");
            }
            #endregion

            #region 排序
            if (!String.IsNullOrEmpty(sort))
                sort = " ORDER BY " + ParseSort(sort, "");
            else
                sort = " ORDER BY " + MapColumn(Alias.Member.InnerID) + " DESC";
            #endregion

            #region 构造SQL语句
            StringBuilder sql = new StringBuilder("SELECT");
            if (page > 0 && pageSize > 0)
                sql.Append(" TOP " + (page * pageSize));
            sql.Append(" " + MapColumn(Alias.Member.ID));
            sql.Append("," + MapColumn(Alias.Member.InnerID));
			sql.Append("," + MapColumn(Alias.Member.No));
			sql.Append("," + MapColumn(Alias.Member.LevelID));
			sql.Append("," + MapColumn(Alias.Member.UserID));
			sql.Append("," + MapColumn(Alias.Member.UserName));
			sql.Append("," + MapColumn(Alias.Member.UserNickname));
			sql.Append("," + MapColumn(Alias.Member.UserRealName));
			sql.Append("," + MapColumn(Alias.Member.UserMobile));
			sql.Append("," + MapColumn(Alias.Member.UserHead));
			sql.Append("," + MapColumn(Alias.Member.AgentID));
			sql.Append("," + MapColumn(Alias.Member.AgentName));
			sql.Append("," + MapColumn(Alias.Member.AgentNickname));
			sql.Append("," + MapColumn(Alias.Member.AgentHead));
			sql.Append("," + MapColumn(Alias.Member.ExpiredTime));
            sql.Append("," + MapColumn(Alias.Member.CreatorID));
			sql.Append("," + MapColumn(Alias.Member.CreatorName));
			sql.Append("," + MapColumn(Alias.Member.CreateTime));
			sql.Append("," + MapColumn(Alias.Member.IsDeleted));
            sql.Append(" FROM " + MapTable(Alias.Objects.Member));
            sql.Append(where + sort);
            Command.CommandText = sql.ToString();
            #endregion

            #region 执行SQL语句并获取记录
            int curIndex = 0, rowIndex = 0;
            int startIndex = (page - 1) * pageSize;
            DataReader = Command.ExecuteReader();
            while (DataReader.Read())
            {
                if (pageSize != -1)
                {
                    if (curIndex < startIndex)
                    {
                        curIndex++;
                        continue;
                    }

                    if (rowIndex == pageSize)
                        break;
                }

                DataRow row = result.NewRow();
                int i = 0;
                row[Alias.Member.ID] = DataReader[i++];
                row[Alias.Member.InnerID] = DataReader[i++];
				row[Alias.Member.No] = DataReader[i++];
				row[Alias.Member.LevelID] = DataReader[i++];
				row[Alias.Member.UserID] = DataReader[i++];
				row[Alias.Member.UserName] = DataReader[i++];
				row[Alias.Member.UserNickname] = DataReader[i++];
				row[Alias.Member.UserRealName] = DataReader[i++];
				row[Alias.Member.UserMobile] = DataReader[i++];
				row[Alias.Member.UserHead] = DataReader[i++];
				row[Alias.Member.AgentID] = DataReader[i++];
				row[Alias.Member.AgentName] = DataReader[i++];
				row[Alias.Member.AgentNickname] = DataReader[i++];
				row[Alias.Member.AgentHead] = DataReader[i++];
				row[Alias.Member.ExpiredTime] = DataReader[i++];
                row[Alias.Member.CreatorID] = DataReader[i++];
				row[Alias.Member.CreatorName] = DataReader[i++];
				row[Alias.Member.CreateTime] = DataReader[i++];
				row[Alias.Member.IsDeleted] = DataReader[i++];
                result.Rows.Add(row);

                rowIndex++;
            }
            DataReader.Close();
            #endregion

            return result;
        }
        public int GetRecordCount(string condition)
        {
            #region 条件
            Command.Parameters.Clear();
            StringBuilder where = new StringBuilder();
            if (!String.IsNullOrEmpty(condition))
            {
                where.Append(" WHERE " + Alias.Member.RowLang + " LIKE '%|" + Lang + "|%' AND ");
                where.Append("(" + condition + ")");

                ParseCondition(where, "");
            }
            else
            {
                where.Append(" WHERE " + MapColumn(Alias.Member.RowLang) + " LIKE '%|" + Lang + "|%'");
            }

            #endregion

            Command.CommandText = "SELECT COUNT(*) FROM " + MapTable(Alias.Objects.Member) + where;
            return Convert.ToInt32(Command.ExecuteScalar());
        }
    }
}
